using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using BONSAI.Common;
using BONSAI.Common.DTO;

namespace BONSAI.DAL
{
	public class ProductsDAO
	{
		public const string PRODUCTS_GET_BY_ID = "ProductsGetByID";
		public const string PRODUCTS_GET_ALL = "ProductsGetAll";
		public const string PRODUCTS_INSERT = "ProductsInsert";
		public const string PRODUCTS_UPDATE_BY_ID = "ProductsUpdateByID";
		public const string PRODUCTS_DELETE_BY_ID = "ProductsDeleteByID";

		public const string PRODUCTSID = "ProductsID";
		public const string NAMEFULL = "NameFull";
        public const string PRODUCTSCODE = "ProductsCode";
		public const string PRICEOLD = "PriceOld";
		public const string PRICENEW = "Pricenew";
		public const string THUMBNAIL = "thumbnail";
		public const string PREVIEW = "preview";
		public const string ISNEW = "Isnew";
		public const string DESCRIPTION = "Description";
		public const string TYPEPRODUCTSID = "TypeProductsID";
		public const string CREATEDATE = "Createdate";
        //09.29.2011
        public const string PRODUCTS_GET_BY_NEW = "ProductsGetByNew";

		#region region properties
		public Database Database { get; set; }
		#endregion

		#region Constructor
		public ProductsDAO()
		{
			Database = DatabaseFactory.CreateDatabase(BONSAIConstants.BONSAI_CONNECTION_STRING);
		}
		#endregion


		public ProductsDTO GetByID(string id)
		{
			var command = Database.GetStoredProcCommand(PRODUCTS_GET_BY_ID);
			try
			{
				Database.AddInParameter(command, PRODUCTSID, DbType.String, id);
				var dataReader = Database.ExecuteReader(command);
				while (dataReader.Read())
				{
					var item = GetProductsFromDataReader(dataReader);
					return item;
				}
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			return null;
		}


		public IList<ProductsDTO> GetAll()
		{
			var command = Database.GetStoredProcCommand(PRODUCTS_GET_ALL);
			try
			{
				var dataReader = Database.ExecuteReader(command);
				var lstItem = new List<ProductsDTO>();
				while (dataReader.Read())
				{
					var item = GetProductsFromDataReader(dataReader);
					lstItem.Add(item);
				}
				return lstItem;
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}


		public string Insert(ProductsDTO products)
		{
			var command = Database.GetStoredProcCommand(PRODUCTS_INSERT);
			try
			{
				Database.AddOutParameter(command, PRODUCTSID, DbType.String, 36);
				Database.AddInParameter(command, NAMEFULL, DbType.String, products.NameFull);
                Database.AddInParameter(command, PRODUCTSCODE, DbType.String, products.ProductsCode);
                Database.AddInParameter(command, PRICEOLD, DbType.Double, products.PriceOld);
				Database.AddInParameter(command, PRICENEW, DbType.Double, products.Pricenew);
				Database.AddInParameter(command, THUMBNAIL, DbType.String, products.thumbnail);
				Database.AddInParameter(command, PREVIEW, DbType.String, products.preview);
				Database.AddInParameter(command, ISNEW, DbType.Boolean, products.Isnew);
				Database.AddInParameter(command, DESCRIPTION, DbType.String, products.Description);
				Database.AddInParameter(command, TYPEPRODUCTSID, DbType.String, products.TypeProductsID);
				Database.AddInParameter(command, CREATEDATE, DbType.DateTime, products.Createdate);
				var dataReader = Database.ExecuteReader(command);
				return Database.GetParameterValue(command, PRODUCTSID).ToString();
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			return null;
		}


		public void UpdateByID(ProductsDTO products)
		{
			var command = Database.GetStoredProcCommand(PRODUCTS_UPDATE_BY_ID);
			try
			{
				Database.AddInParameter(command, PRODUCTSID, DbType.String, products.ProductsID);
				Database.AddInParameter(command, NAMEFULL, DbType.String, products.NameFull);
                Database.AddInParameter(command, PRODUCTSCODE, DbType.String, products.ProductsCode);
                Database.AddInParameter(command, PRICEOLD, DbType.Double, products.PriceOld);
				Database.AddInParameter(command, PRICENEW, DbType.Double, products.Pricenew);
				Database.AddInParameter(command, THUMBNAIL, DbType.String, products.thumbnail);
				Database.AddInParameter(command, PREVIEW, DbType.String, products.preview);
				Database.AddInParameter(command, ISNEW, DbType.Boolean, products.Isnew);
				Database.AddInParameter(command, DESCRIPTION, DbType.String, products.Description);
				Database.AddInParameter(command, TYPEPRODUCTSID, DbType.String, products.TypeProductsID);
				Database.AddInParameter(command, CREATEDATE, DbType.DateTime, products.Createdate);
				var dataReader = Database.ExecuteReader(command);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}


		public void DeleteByID(string productsid)
		{
			var command = Database.GetStoredProcCommand(PRODUCTS_DELETE_BY_ID);
			try
			{
				Database.AddInParameter(command, PRODUCTSID, DbType.String, productsid);
				var dataReader = Database.ExecuteReader(command);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}


		private static ProductsDTO GetProductsFromDataReader(IDataRecord dataReader)
		{
			var item = new ProductsDTO();
			try
			{

				item.ProductsID = (string.IsNullOrEmpty(Convert.ToString(dataReader[PRODUCTSID]))) ? "" : Convert.ToString(dataReader[PRODUCTSID]);
				item.NameFull = (string.IsNullOrEmpty(Convert.ToString(dataReader[NAMEFULL]))) ? "" : Convert.ToString(dataReader[NAMEFULL]);
                item.ProductsCode = (string.IsNullOrEmpty(Convert.ToString(dataReader[PRODUCTSCODE]))) ? "" : Convert.ToString(dataReader[PRODUCTSCODE]);
				item.PriceOld = (string.IsNullOrEmpty(Convert.ToString(dataReader[PRICEOLD]))) ? 0 : Convert.ToInt32(dataReader[PRICEOLD].ToString());
				item.Pricenew = (string.IsNullOrEmpty(Convert.ToString(dataReader[PRICENEW]))) ? 0 : Convert.ToInt32(dataReader[PRICENEW].ToString());
				item.thumbnail = (string.IsNullOrEmpty(Convert.ToString(dataReader[THUMBNAIL]))) ? "" : Convert.ToString(dataReader[THUMBNAIL]);
				item.preview = (string.IsNullOrEmpty(Convert.ToString(dataReader[PREVIEW]))) ? "" : Convert.ToString(dataReader[PREVIEW]);

			    item.Isnew = false;
                if (!string.IsNullOrEmpty(dataReader[ISNEW].ToString()))
                {
                    item.Isnew = Convert.ToBoolean(dataReader[ISNEW].ToString());
                }
			    item.Description = (string.IsNullOrEmpty(Convert.ToString(dataReader[DESCRIPTION]))) ? "" : Convert.ToString(dataReader[DESCRIPTION]);
				item.TypeProductsID = (string.IsNullOrEmpty(Convert.ToString(dataReader[TYPEPRODUCTSID]))) ? "" : Convert.ToString(dataReader[TYPEPRODUCTSID]);
				item.Createdate = (string.IsNullOrEmpty(Convert.ToString(dataReader[CREATEDATE]))) ? DateTime.MinValue : DateTime.Parse(dataReader[CREATEDATE].ToString());
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			return item;
		}
        //09.29.2011
        public IList<ProductsDTO> GetNew(Boolean codition)
        {
            var command = Database.GetStoredProcCommand(PRODUCTS_GET_BY_NEW);
            try
            {
                Database.AddInParameter(command, ISNEW, DbType.Boolean, codition);
                var dataReader = Database.ExecuteReader(command);
                var lstItem = new List<ProductsDTO>();
                while (dataReader.Read())
                {
                    var item = GetProductsFromDataReader(dataReader);
                    lstItem.Add(item);
                }
                return lstItem;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
	}
}

